<?php
/**
 * Created by PhpStorm.
 * User: JSQ-SL
 * Date: 2018/11/19
 * Time: 15:37
 */

namespace app\index\table;

use think\Db;
use app\index\service\ExcelService;

class RateInTable extends BaseTable
{
    protected static $view = 'V_RATE_IN_INFO';
    protected static $table = 'RATE_IN_INFO';
    protected static $pk = 'R_IN_ID';
    protected static $configTable = 'RATE_IN_CONFIG';

    static public function table(){
        return Db::table(static::$view);
    }

    static public function add($data){
        $data[self::$pk] = static::getNextId();
        Db::table(static::$table)->insert($data);
    }

    static public function update($where,$data){
        Db::table(static::$table)->where($where)->update($data);
    }

    static public function delete($where){
        return Db::table(static::$table)->where($where)->delete();
    }

    public static function getConfigAdd(){
        return Db::table(self::$configTable)->where(['ADD_FLAG'=>'1'])->order('ID ASC')->select();
    }

    public static function getConfigEdit(){
        return Db::table(self::$configTable)->where(['EDIT_FLAG'=>'1'])->order('ID ASC')->select();
    }

    public static function getConfigPrint(){
        return Db::table(self::$configTable)->where(['PRINT_FLAG'=>'1'])->order('ID ASC')->select();
    }

    public static function getNextId(){
        $year = date('Y');
        $numMaxRecord = Db::table(static::$table)
            ->field('max(SUBSTR('.self::$pk.',5,4)) as NUM')
            ->where([['SUBSTR('.self::$pk.',1,4)','=',$year]])
            ->fetchSql(true)
            ->find();
        $numMaxRecord = Db::query($numMaxRecord);
        $num = $numMaxRecord[0]['NUM'];
        if ($num == NULL) {
            $num = 0;
        }
        $num++;
        return $year.sprintf("%04d", $num);
    }

    public static function exportExcel($data,$fileName){

        $fieldList = [
            ['title'=>'学院','field'=>'SHORTNAME'],
            ['title'=>'年份','field'=>'YEAR'],
            ['title'=>'教职工总数','field'=>'HR_NUM_JZ'],
            ['title'=>'专职教师数','field'=>'HR_NUM_ZJ'],
            ['title'=>'行政人员总数','field'=>'HR_NUM_XZ'],
            ['title'=>'教辅人员数','field'=>'HR_NUM_JF'],
            ['title'=>'实验人员数','field'=>'HR_NUM_SY'],
            ['title'=>'外聘教师数','field'=>'HR_NUM_WP'],
            ['title'=>'博士学历占专职教师比例','field'=>'HR_RATE_BS'],
            ['title'=>'副高以上专职教师比例','field'=>'HR_RATE_FG'],
            ['title'=>'35-45岁教师占专职教师比列','field'=>'HR_RATE_35TO45'],
            ['title'=>'博士生导师数','field'=>'HR_NUM_BD'],
            ['title'=>'硕士生导师数','field'=>'HR_NUM_SD'],
            ['title'=>'仪器设备总值','field'=>'MR_FEE_SB'],
            ['title'=>'教学科研仪器设备总值','field'=>'MR_FEE_JXKY'],
            ['title'=>'贵重仪器设备占总值比','field'=>'MR_RATE_GZ'],
            ['title'=>'学院用房总面积','field'=>'MR_AREA_USE'],
            ['title'=>'教学科研实验用房面积','field'=>'MR_AREA_JXKY'],
            ['title'=>'总支出','field'=>'FR_FEE_TOTAL'],
            ['title'=>'教育事业支出','field'=>'FR_FEE_JY'],
            ['title'=>'科研事业支出','field'=>'FR_FEE_KY'],
            ['title'=>'生均教育事业支出','field'=>'FR_FEE_SJ'],
            ['title'=>'博士点数','field'=>'SR_P_BS'],
            ['title'=>'硕士点数','field'=>'SR_P_SS'],
            ['title'=>'博士后科研流动站','field'=>'SR_BSH'],
            ['title'=>'进入一级学科排名前20%学科数','field'=>'SR_XK_1'],
            ['title'=>'“双一流”建设学科数','field'=>'SR_XK_2'],
            ['title'=>'省级重点学科数','field'=>'SR_XK_3'],
            ['title'=>'国家级重点实验室、研究中心、科研基地数（当年）','field'=>'SR_KY_1'],
            ['title'=>'省部级重点实验室、研究中心、科研基地数（当年）','field'=>'SR_KY_2']
        ];

        $excel = new ExcelService();
        $excel->exportExcel($data,$fileName,$fieldList);
    }

    const RATE_IN_FIELD_LIST = [
        "COLLEGE_ID","YEAR","HR_NUM_JZ","HR_NUM_ZJ","HR_NUM_XZ","HR_NUM_JF","HR_NUM_SY","HR_NUM_WP","HR_RATE_BS","HR_RATE_FG","HR_RATE_35TO45","HR_NUM_BD","HR_NUM_SD","MR_FEE_SB","MR_FEE_JXKY","MR_RATE_GZ","MR_AREA_USE","MR_AREA_JXKY","FR_FEE_TOTAL","FR_FEE_JY","FR_FEE_KY","FR_FEE_SJ","SR_P_BS","SR_P_SS","SR_BSH","SR_XK_1","SR_XK_2","SR_XK_3","SR_KY_1","SR_KY_2",
    ];
}